
-- 将外部表设为 删表清文件模式
alter table jms_dm.dm_scan_bagging_true_summary_dt set tblproperties ('external.table.purge'='true');

--删除表，同步会删除文件；文件会临时存储到回收站
drop table jms_dm.dm_scan_bagging_true_summary_dt;
CREATE EXTERNAL TABLE jms_dm.dm_scan_bagging_true_summary_dt
(
    `agent_code`         string COMMENT '代理区code',
    `agent_name`         string COMMENT '代理区名称',
    `is_arrival_port`    bigint COMMENT '是否进港建包 1是0否',
    `bag_scan_site_code` string COMMENT '建包网点code',
    `bag_scan_site_name` string COMMENT '建包网点名称',
    `bag_scan_site_type` bigint COMMENT '建包网点类型',
    `parent_network_code`         string COMMENT '上级网点code',
    `parent_network_name`         string COMMENT '上级网点',
    `bag_cnt`            bigint COMMENT '实建包票数',
    `bag_err_cnt`        bigint COMMENT '错建包票数',
    `bag_err_rate`       decimal(16, 4) COMMENT '错建包率',
    `bag_sys_err_cnt`    bigint COMMENT '包牌三段码错误票数',
    `bill_sys_err_cnt`   bigint COMMENT '运单三段码错误票数',
    `dev_err_cnt`        bigint COMMENT '设备建错包票数',
    `tab_err_cnt`        bigint COMMENT '操作建错包票数',
    `reback_cnt`         bigint COMMENT '实建包票数',
    `resend_cnt`         bigint COMMENT '错建包票数',
    `head_err_cnt`       bigint COMMENT '实建包票数',
    `dup_err_cnt`        bigint COMMENT '错建包票数',
    `trace_err_cnt`      bigint COMMENT '包件不关联票数',
    `statistic_date`     date COMMENT '统计日期'
)COMMENT '建包准确率统计'
    PARTITIONED BY ( `dt` string COMMENT '统计日期')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_scan_bagging_true_summary_dt'
    TBLPROPERTIES (
        'discover.partitions' = 'false',
        'parquet.column.index.access' = 'true'
        );